﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using Lm.Common.ExtensionMethods;
using MySql.Data.MySqlClient;

namespace Lm.Common.DAL.MySql
{
    public class MySqlDAL : Lm.Common.DAL.DALBase
    {
        public MySqlDAL(string connectionString)
            : base(new MySqlConnection(connectionString))
        { }

        protected override System.Data.Common.DbCommand CreateCommand()
        {
            return new MySqlCommand();
        }

        protected override System.Data.Common.DbDataAdapter CreateAdapter(System.Data.Common.DbCommand cmd)
        {
            return new MySqlDataAdapter((MySqlCommand)cmd);
        }

        protected StringBuilder ParseWhere<T>(Expression<Func<T, bool>>[] conditions, MySqlParameterManager parameterManager) where T : Lm.Common.DAL.IModelBase
        {
            StringBuilder sbWhere = new StringBuilder();
            sbWhere.Append(" 1=1 ");
            foreach (var c in conditions)
            {
                sbWhere.Append(" and " + new MySqlExpressionParser(parameterManager).Parse(c) + " ");
            }
            return sbWhere;
        }

        protected override long ExecuteInsert<T>(Expression<Func<T>> fields)
        {
            var parameterManager = new MySqlParameterManager(new List<MySqlParameter>());
            LambdaExpression lmbd = fields as LambdaExpression;
            if (lmbd.Body.NodeType != ExpressionType.MemberInit)
            { throw new Exception("执行insert时，表达式树参数错误，参考：dal.Insert<user2>(() => new user2() { City = u.City });" + fields.Body.ToString()); }
            MemberInitExpression memberInit = lmbd.Body as MemberInitExpression;
            if (memberInit.Bindings.Count == 0)
            { throw new Exception("没有要写入的数据"); }

            StringBuilder sbNames = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            System.Reflection.PropertyInfo identityProperty = null;
            foreach (var bind in memberInit.Bindings)
            {
                if (bind.BindingType != MemberBindingType.Assignment)
                { throw new Exception("执行insert时，表达式树参数错误，参考：dal.Insert<user2>(() => new user2() { City = u.City });" + fields.Body.ToString()); }
                var assignment = bind as MemberAssignment;

                var p = typeof(T).GetProperty(assignment.Member.Name);
                var identityAttributeList = p.GetCustomAttributes(typeof(IdentityAttribute), false);
                if (identityAttributeList.Length > 0)
                {
                    if (identityProperty == null)
                    {
                        if (p.PropertyType != typeof(Int64))
                        { throw new Exception("只支持Int64（long）自增一属性"); }
                        identityProperty = p;
                    }
                    else
                    { throw new Exception("Model只能包含一个自增一属性"); }
                    continue;
                }

                sbNames.Append("`" + assignment.Member.Name + "`,");
                string value = new MySqlExpressionParser(parameterManager).Parse(assignment.Expression);
                sbValues.Append(value + ",");
            }
            string insertString = "insert into `" + typeof(T).Name + "` (" + sbNames.ToString().TrimEnd(',') + ") values (" + sbValues.ToString().TrimEnd(',') + ");";

            if (identityProperty != null)
            {
                insertString += "\r\n SELECT @@identity;";
                var obj = this.ExecuteScalar(insertString, parameterManager.SqlParameterList.ToArray());
                if (obj == DBNull.Value)
                { throw new Exception("没有返回自增一Id"); }
                return Convert.ToInt64(obj);
            }
            else
            {
                return this.ExecuteNonQuery(insertString, parameterManager.SqlParameterList.ToArray());
            }
        }

        protected override bool ExecuteInsert<T>(T model)
        {
            List<MySqlParameter> parameterList = new List<MySqlParameter>();
            var Properties = model.GetType().GetProperties();

            StringBuilder sbNames = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            sbNames.Append("(");
            sbValues.Append("(");
            System.Reflection.PropertyInfo identityProperty = null;
            foreach (var p in Properties)
            {
                var identityAttributeList = p.GetCustomAttributes(typeof(IdentityAttribute), false);
                if (identityAttributeList.Length > 0)
                {
                    if (identityProperty == null)
                    {
                        if (p.PropertyType != typeof(Int64))
                        { throw new Exception("只支持Int64（long）自增一属性"); }
                        identityProperty = p;
                    }
                    else
                    { throw new Exception("Model只能包含一个自增一属性"); }
                    continue;
                }

                sbNames.Append("`" + p.Name + "`,");
                sbValues.Append("@" + p.Name + ",");
                var Parameter = new MySqlParameter("@" + p.Name, p.GetValue(model, null));
                if (Parameter.Value == null)
                {
                    Parameter.Value = DBNull.Value;
                }
                parameterList.Add(Parameter);
            }
            if (parameterList.Count == 0)
            { throw new Exception("没有任何要写入数据库的数据"); }
            sbNames.Remove(sbNames.Length - 1, 1);
            sbValues.Remove(sbValues.Length - 1, 1);
            sbNames.Append(")");
            sbValues.Append(")");

            var commandText = "insert into `" + model.GetType().Name + "` " + sbNames + " values " + sbValues + ";";
            if (identityProperty != null)
            {
                commandText += "\r\n SELECT @@identity;";
                var obj = this.ExecuteScalar(commandText, parameterList.ToArray());
                if (obj == DBNull.Value)
                { throw new Exception("没有返回自增一Id"); }
                identityProperty.SetValue(model, Convert.ToInt64(obj), null);
                return true;
            }
            else
            {
                return this.ExecuteNonQuery(commandText, parameterList.ToArray()) == 1;
            }
        }

        public override int ExecuteDelete<T>(params Expression<Func<T, bool>>[] conditions)
        {
            var parameterManager = new MySqlParameterManager(new List<MySqlParameter>());
            StringBuilder sbWhere = ParseWhere<T>(conditions, parameterManager);
            string deleteString = "delete from `" + typeof(T).Name + "` where " + sbWhere.ToString() + ";";
            return this.ExecuteNonQuery(deleteString, parameterManager.SqlParameterList.ToArray());
        }

        public override int ExecuteUpdate<T>(T model, params Expression<Func<T, bool>>[] conditions)
        {
            //List<SqlParameter> parameterList = new List<SqlParameter>();
            var parameterManager = new MySqlParameterManager(new List<MySqlParameter>());
            var Properties = model.GetType().GetProperties();
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("update `" + model.GetType().Name + "` set ");

            foreach (var p in Properties)
            {
                var identityAttributeList = p.GetCustomAttributes(typeof(IdentityAttribute), false);
                if (identityAttributeList.Length > 0)
                { continue; }

                MySqlParameter parameter = parameterManager.Create(p.GetValue(model, null));
                sbSql.Append("`" + p.Name + "`=" + parameter.ParameterName + ",");
                if (parameter.Value == null)
                { parameter.Value = DBNull.Value; }
            }
            if (parameterManager.SqlParameterList.Count == 0)
            { throw new Exception("没有任何要更新的数据"); }

            string commandText = sbSql.ToString().TrimEnd(',') + " where " + this.ParseWhere<T>(conditions, parameterManager).ToString() + ";";
            return this.ExecuteNonQuery(commandText, parameterManager.SqlParameterList.ToArray());
        }

        public override int ExecuteUpdate<T>(Expression<Func<T, T>> fields, params Expression<Func<T, bool>>[] conditions)
        {
            var parameterManager = new MySqlParameterManager(new List<MySqlParameter>());
            LambdaExpression lmbd = fields as LambdaExpression;
            if (lmbd.Body.NodeType != ExpressionType.MemberInit)
            { throw new Exception("执行update时，表达式树参数错误，参考：dal.Insert<user2>(() => new user2() { City = u.City });" + fields.Body.ToString()); }
            MemberInitExpression memberInit = lmbd.Body as MemberInitExpression;
            if (memberInit.Bindings.Count == 0)
            { throw new Exception("没有要写入的数据"); }

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("update `" + typeof(T).Name + "` set ");
            var parser = new MySqlExpressionParser(parameterManager);
            foreach (var bind in memberInit.Bindings)
            {
                if (bind.BindingType != MemberBindingType.Assignment)
                { throw new Exception("执行insert时，表达式树参数错误，参考：dal.Insert<user2>(() => new user2() { City = u.City });" + fields.Body.ToString()); }
                var assignment = bind as MemberAssignment;

                var p = typeof(T).GetProperty(assignment.Member.Name);
                var identityAttributeList = p.GetCustomAttributes(typeof(IdentityAttribute), false);
                if (identityAttributeList.Length > 0)
                { continue; }

                sbSql.Append("`" + assignment.Member.Name + "` = ");
                string value = parser.Parse(assignment.Expression);
                sbSql.Append(value + ",");
            }
            string updateCommandText = sbSql.ToString().TrimEnd(',') + " where " + this.ParseWhere<T>(conditions, parameterManager).ToString() + ";";
            return this.ExecuteNonQuery(updateCommandText, parameterManager.SqlParameterList.ToArray());
        }

        public override System.Data.DataTable QueryDataTable<T>(Expression<Func<T>> selectFields, Expression<Func<T>> orderByFields, OrderByOption option, params Expression<Func<T, bool>>[] conditions)
        {
            var parameterManager = new MySqlParameterManager(new List<MySqlParameter>());
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select ");
            {
                if (selectFields == null)
                {
                    var properties = typeof(T).GetProperties();
                    if (properties.Length == 0)
                    { throw new Exception("必须指定要查询的字段"); }
                    foreach (var p in properties)
                    {
                        sbSql.Append("`" + p.Name + "`,");
                    }
                }
                else
                {
                    LambdaExpression lmbd = selectFields as LambdaExpression;
                    if (lmbd.Body.NodeType != ExpressionType.MemberInit)
                    { throw new Exception("执行select时，表达式树参数错误:" + selectFields.Body.ToString()); }
                    MemberInitExpression memberInit = lmbd.Body as MemberInitExpression;
                    if (memberInit.Bindings.Count == 0)
                    { throw new Exception("没有要查询的字段"); }

                    foreach (var bind in memberInit.Bindings)
                    {
                        if (bind.BindingType != MemberBindingType.Assignment)
                        { throw new Exception("执行select时，表达式树参数错误:" + selectFields.Body.ToString()); }
                        var assignment = bind as MemberAssignment;
                        sbSql.Append("`" + assignment.Member.Name + "`,");
                    }
                }
                sbSql.Remove(sbSql.Length - 1, 1);
            }
            sbSql.Append(" from ");
            sbSql.Append(" `" + typeof(T).Name + "` ");

            sbSql.Append(" where ");
            sbSql.Append(this.ParseWhere<T>(conditions, parameterManager));

            if (orderByFields != null)
            {
                sbSql.Append(" order by ");
                LambdaExpression lmbd = orderByFields as LambdaExpression;
                if (lmbd.Body.NodeType != ExpressionType.MemberInit)
                { throw new Exception("执行select时，表达式树参数错误:" + orderByFields.Body.ToString()); }
                MemberInitExpression memberInit = lmbd.Body as MemberInitExpression;
                if (memberInit.Bindings.Count == 0)
                { throw new Exception("没有排序字段"); }
                foreach (var bind in memberInit.Bindings)
                {
                    if (bind.BindingType != MemberBindingType.Assignment)
                    { throw new Exception("执行select时，表达式树参数错误:" + selectFields.Body.ToString()); }
                    var assignment = bind as MemberAssignment;
                    sbSql.Append("`" + assignment.Member.Name + "`,");
                }
                sbSql.Remove(sbSql.Length - 1, 1);
                sbSql.Append(" " + option.ToString() + ";");
            }
            return this.QueryDataTable(sbSql.ToString(), parameterManager.SqlParameterList.ToArray());
        }

        public override System.Data.DataTable QueryDataTable<T>(long pageSize, long pageIndex, out long recordCount, Expression<Func<T>> selectFields, Expression<Func<T>> orderByFields, OrderByOption option, params Expression<Func<T, bool>>[] conditions)
        {
            var parameterManager = new MySqlParameterManager(new List<MySqlParameter>());
            var fields = new StringBuilder();
            if (selectFields == null)
            {
                var properties = typeof(T).GetProperties();
                if (properties.Length == 0)
                { throw new Exception("必须指定要查询的字段"); }
                foreach (var p in properties)
                {
                    fields.Append("`" + p.Name + "`,");
                }
            }
            else
            {
                LambdaExpression lmbd = selectFields as LambdaExpression;
                if (lmbd.Body.NodeType != ExpressionType.MemberInit)
                { throw new Exception("执行select时，表达式树参数错误:" + selectFields.Body.ToString()); }
                MemberInitExpression memberInit = lmbd.Body as MemberInitExpression;
                if (memberInit.Bindings.Count == 0)
                { throw new Exception("没有要查询的字段"); }
                foreach (var bind in memberInit.Bindings)
                {
                    if (bind.BindingType != MemberBindingType.Assignment)
                    { throw new Exception("执行select时，表达式树参数错误:" + selectFields.Body.ToString()); }
                    var assignment = bind as MemberAssignment;
                    fields.Append("`" + assignment.Member.Name + "`,");
                }
            }
            fields.Remove(fields.Length - 1, 1);


            StringBuilder sbSql = new StringBuilder();
            var countSqlHead = " SELECT	count(*) ";
            var querySqlHead = "SELECT " + fields + " ";
            sbSql.Append(" FROM `" + typeof(T).Name + "` ");
            sbSql.Append(" where ");
            sbSql.Append(this.ParseWhere<T>(conditions, parameterManager));
            if (orderByFields != null)
            {
                sbSql.Append(" ORDER BY ");
                LambdaExpression lmbd = orderByFields as LambdaExpression;
                if (lmbd.Body.NodeType != ExpressionType.MemberInit)
                { throw new Exception("执行select时，表达式树参数错误:" + orderByFields.Body.ToString()); }
                MemberInitExpression memberInit = lmbd.Body as MemberInitExpression;
                if (memberInit.Bindings.Count == 0)
                { throw new Exception("必须指定排序字段"); }
                foreach (var bind in memberInit.Bindings)
                {
                    if (bind.BindingType != MemberBindingType.Assignment)
                    { throw new Exception("执行select时，表达式树参数错误:" + selectFields.Body.ToString()); }
                    var assignment = bind as MemberAssignment;
                    sbSql.Append("`" + assignment.Member.Name + "`,");
                }
                sbSql.Remove(sbSql.Length - 1, 1);
                sbSql.Append(" " + option + " ");
            }
            recordCount = Convert.ToInt64(this.QueryDataTable(countSqlHead + sbSql.ToString() + ";", parameterManager.SqlParameterList.ToArray()).Rows[0][0]);
            sbSql.Append(" LIMIT " + pageSize * (pageIndex - 1) + "," + pageSize + " ;");
            return this.QueryDataTable(querySqlHead + sbSql, parameterManager.SqlParameterList.Select(t => new MySqlParameter(t.ParameterName, t.Value)).ToArray());
        }

        public override int Count<T>(params Expression<Func<T, bool>>[] conditions)
        {
            var parameterManager = new MySqlParameterManager(new List<MySqlParameter>());
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" select count(*) from ");
            sbSql.Append(" `" + typeof(T).Name + "` ");

            sbSql.Append(" where ");
            sbSql.Append(this.ParseWhere<T>(conditions, parameterManager) + ";");
            return this.QueryDataTable(sbSql.ToString(), parameterManager.SqlParameterList.ToArray()).Rows[0][0].ToString().ToInt32();
        }
    }
}
